package com.hdg.boot.excel_listener;

import com.hdg.boot.dto.ExcelAddDownloadDto;
import com.hdg.boot.dto.ExcelUpdateDownloadDto;
import com.hdg.boot.mapper.NewAddTableMapper;
import com.hdg.boot.mapper.NewUpdateTableMapper;
import com.hdg.boot.mapper.XirMdMapper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author zlj
 * @Date 2023/03/17 16:10
 * @Description 生成sql
 */
@Service
public class CreatSqlService {
    private static final Logger logger = LoggerFactory.getLogger(UploadListener.class);
    @Autowired
    NewUpdateTableMapper newUpdateTableMapper;

    @Autowired
    NewAddTableMapper newAddTableMapper;
    @Autowired
    XirMdMapper xirMdMapper;
    static FileWriter fileWriter1 = null;
    static FileWriter fileWriter2 = null;
    static FileWriter fileWriter5 = null;
    public static final String TRD = "t_xir_trd";
    public static final String CRMS = "t_xir_crms";
    public static final String RISK = "t_xir_risk";
    public static final String MD = "t_xir_md";

    static {
        try {
            fileWriter1 = new FileWriter("src/main/java/com/hdg/boot/excel_listener/filepackge/drop.sql");
            fileWriter2 = new FileWriter("src/main/java/com/hdg/boot/excel_listener/filepackge/create.sql");
            fileWriter5 = new FileWriter("src/main/java/com/hdg/boot/excel_listener/filepackge/alter.sql");
        } catch (
                IOException e) {
            e.printStackTrace();
        }
    }

    static BufferedWriter dropTable = new BufferedWriter(fileWriter1);
    static BufferedWriter createTable = new BufferedWriter(fileWriter2);
    static BufferedWriter bufferedTable = new BufferedWriter(fileWriter5);

    public void creatSql() throws IOException {
        List<ExcelUpdateDownloadDto> all = newUpdateTableMapper.getAll();
        BufferedWriter bufferedWriter = null;
        List<String> res = new ArrayList<>();
        for (ExcelUpdateDownloadDto e :
                all) {
            if (e.getEnglishName().startsWith("V")) {
                continue;
            }
            String str = "";
            if (e.getYn().equals("新增")) {
                str = insertColumn(e);
            } else if (e.getYn().equals("修改")) {
                str = update(e);
            } else if (e.getYn().equals("删除")) {
                str = deleteColumn(e);
            }
            if (!res.contains(str) && str != null) {
                res.add(str);
            }
        }
        for (String s : res) {
            logger.info("修改  " + s);
            bufferedTable.write(s);
            bufferedTable.newLine();
            bufferedTable.flush();
        }
        bufferedTable.close();
    }


    public String insertColumn(ExcelUpdateDownloadDto excelUpdateDownloadDto) {
        if (excelUpdateDownloadDto.getColumnType().equals("UNDEFINED")) {
            return null;
        }
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" ALTER TABLE  ").append(excelUpdateDownloadDto.getOwner()).append(".").append(excelUpdateDownloadDto.getEnglishName()).
                append(" ADD ").append(excelUpdateDownloadDto.getColumnName()).append("  ")
                .append(excelUpdateDownloadDto.getColumnType()).append(" (" + excelUpdateDownloadDto.getColumnLength());

        return getString(excelUpdateDownloadDto, stringBuffer);
    }

    public String update(ExcelUpdateDownloadDto excelUpdateDownloadDto) {
        if (excelUpdateDownloadDto.getColumnType().equals("UNDEFINED")) {
            return null;
        }
        StringBuffer stringBuffer = new StringBuffer();
        if (excelUpdateDownloadDto.getEnglishName().equals("V_WMPS_REG_TEMP")) {
        } else if (excelUpdateDownloadDto.getEnglishName().startsWith("V")) {
            return null;
        }
        stringBuffer.append(" ALTER TABLE ").append(excelUpdateDownloadDto.getOwner()).append(".").append(excelUpdateDownloadDto.getEnglishName())
                .append(" MODIFY  ").append(excelUpdateDownloadDto.getColumnName()).append("  ")
                .append(excelUpdateDownloadDto.getColumnType()).append(" (" + excelUpdateDownloadDto.getColumnLength());
        return getString(excelUpdateDownloadDto, stringBuffer);

    }

    private String getString(ExcelUpdateDownloadDto excelUpdateDownloadDto, StringBuffer stringBuffer) {
        String scaleLength = excelUpdateDownloadDto.getScaleLength();
        if (scaleLength != null && !scaleLength.equals("") && Integer.valueOf(scaleLength.trim()) > 0) {
            stringBuffer.append("," + excelUpdateDownloadDto.getScaleLength());
        }
        stringBuffer.append(") ");
        stringBuffer.append(" ;");
        stringBuffer.append(" \n COMMENT ON COLUMN " + excelUpdateDownloadDto.getOwner() + "." + excelUpdateDownloadDto.getEnglishName() + "." + excelUpdateDownloadDto.getColumnName() + " IS '" + excelUpdateDownloadDto.getColumnNameChinese() + "' ;");
        return stringBuffer.toString();
    }

    private String deleteColumn(ExcelUpdateDownloadDto excelUpdateDownloadDto) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" ALTER TABLE ").append(excelUpdateDownloadDto.getOwner()).append(".").append(excelUpdateDownloadDto.getEnglishName()).append("  DROP  COLUMN ")
                .append(excelUpdateDownloadDto.getColumnName()).append(";");
        return stringBuffer.toString();

    }

    public void creatTableSql() throws IOException {
        List<ExcelAddDownloadDto> all = newAddTableMapper.getAll();
        List<String> res = new ArrayList<>();
        List<String> addflag = new ArrayList<>();
        for (ExcelAddDownloadDto e :
                all) {
            if (e.getEnglishName().startsWith("V")) {
                continue;
            }
            String str = "";
            if (e.getYn().equals("新增")) {
                str = addTable(e);
                if (!addflag.contains(str)) {
                    addflag.add(str);
                }
            } else if (e.getYn().equals("删除")) {
                str = deleteTable(e);
                if (!res.contains(str)) {
                    res.add(str);
                }
            } else logger.error("错误！！ 没有新增或删除");

        }
        for (String s : res) {
            logger.info("删除" + s);
            dropTable.write(s);
            dropTable.newLine();
            dropTable.flush();
        }
        dropTable.close();
        for (String s : addflag) {
            logger.info("新增" + s);
            createTable.write(s);
            createTable.newLine();
            createTable.flush();
        }
        createTable.close();
    }

    private String addTable(ExcelAddDownloadDto excelAddDownloadDto) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" CREATE TABLE ").append(excelAddDownloadDto.getOwner()).append(".").append(excelAddDownloadDto.getEnglishName() + " (column_name  VARCHAR(100) );");
        return stringBuffer.toString();
    }

    private String deleteTable(ExcelAddDownloadDto excelAddDownloadDto) {
        String owner1 = excelAddDownloadDto.getOwner();
        String tableName = getTableName(owner1);
        Integer table = xirMdMapper.isTable(tableName, excelAddDownloadDto.getEnglishName());
        StringBuffer stringBuffer = new StringBuffer();
        if (table == 0) {
            stringBuffer.append("DROP TABLE ").append(excelAddDownloadDto.getOwner()).append(".").append(excelAddDownloadDto.getEnglishName() + " ;");
        }
        stringBuffer.append(" ");
        return stringBuffer.toString();
    }
    private String getTableName(String fileName) {
        if (fileName.equals("XIR_TRD")) {
            return TRD;
        } else if (fileName.equals("XIR_CRMS")) {
            return CRMS;
        } else if (fileName.equals("XIR_RISK")) {
            return RISK;
        } else if (fileName.equals("XIR_MD")) {
            return MD;
        } else {
            throw new RuntimeException("该表不存在!");
        }
    }
}
